/*use [master]
GO
IF EXISTS (SELECT 1 FROM master..sysdatabases where name = 'BDFinal_Peredo' )
	DROP DATABASE BDFinal_Peredo
GO
CREATE DATABASE BDFinal_Peredo
GO
USE [BDFinal_Peredo]
GO

create table Persona (codigo char(15)
					, Nombre varchar(max)
					,Apellido varchar(max)
					,Telefono nvarchar(25)
					, direccion nvarchar(60)
					, ci nvarchar(15))
					
-- 2 --
declare @TempIds table (id varchar(max),idEsp varchar(15))
declare @query nvarchar(max), @Esp varchar(15), @id varchar(max), @esplen varchar(max)

declare qIds Cursor for
		select  A.persontype + left(A.LastName,1)+'-'+ left(A.FirstName,1) + '-' 
	
		 ,cast(A.BusinessEntityID as varchar(max)) from AdventureWorks2012.person.Person as A
		
open qIds

Fetch next from qIds into @Esplen , @id

while @@FETCH_STATUS = 0

begin
while  len(@Esplen) < 15
begin

	if len(@esplen + '0' + '-'+@id) = 15
	begin
	set @esplen = @esplen + '0' + @id
	end
	else
	begin
	set @esplen = @esplen + '0'
	end
end

insert into @TempIds values(@id,@esplen) 

fetch next from qIds into @esplen , @id

end
close qIds
Deallocate qIds 

insert into persona
select t.idEsp,a.FirstName,a.LastName,p.PhoneNumber,ad.AddressLine1,SUBSTRING(h.NationalIDNumber,1,7) from AdventureWorks2012.Person.Person as A
join @TempIds as t on A.BusinessEntityID = t.id
join AdventureWorks2012.HumanResources.Employee as h on h.BusinessEntityID = a.BusinessEntityID 
join AdventureWorks2012.Person.PersonPhone as P on P.BusinessEntityID = a.BusinessEntityID
join AdventureWorks2012.Person.BusinessEntityAddress as U on u.BusinessEntityID = a.BusinessEntityID
join AdventureWorks2012.Person.Address as ad on ad.AddressID = u.AddressID

-- 3 --

create sequence Miseq as int
	minvalue 1 cycle;

create table Asistencia (id int default (next value for Miseq)
						,ci nvarchar(15)
						,Fecha datetime default(current_timestamp)
						,Ingreso bit
						,fechaRegular varchar(max))
						
-- 4 -- 1 ingreso 0 salida
	CREATE PROCEDURE Marcar
	 @Ci nvarchar(15)
	,@SalidaEntrada bit OUTPUT   
AS			
	declare @aux bit, @aux2 nvarchar(15), @fechaReg datetime
	set  @aux = 0

	 
	select @aux2 = count(ci) from asistencia
	where exists (select * from asistencia
				where ci = 1
				and datepart(day,fecha) = DATEPART(day,CURRENT_TIMESTAMP))

	if @aux2= 0 or @aux2 = 2
	begin
	set @aux = 1
	end

	if @aux2 = 0
	begin
	set @fechaReg = DateAdd(hour,8,cast(cast (GETDATE() as Date)as datetime))
	end

	if @aux2 = 1
	begin
	set @fechaReg = DateAdd(hour,12,cast(cast (GETDATE() as Date)as datetime))
	end

	if @aux2 = 2
	begin
	set @fechaReg = DateAdd(hour,14,cast(cast (GETDATE() as Date)as datetime))
	end

	if @aux2 = 3
	begin
	set @fechaReg = DateAdd(hour,18,cast(cast (GETDATE() as Date)as datetime))
	end

insert into Asistencia(ci,fecha,ingreso,fechaRegular) values (@Ci,CURRENT_TIMESTAMP,@aux,@fechaReg)
set @SalidaEntrada = @aux

go  
*/
-- 5 --
	CREATE PROCEDURE Buscar
	 @Ci nvarchar(15)
	,@fechaInicio datetime
	,@fechaFin datetime

AS	

if ci is not null
begin
select * from Asistencia
	where ci = @Ci
	and Fecha >= @fechaInicio
	and Fecha <= @fechaFin		
end
else
begin
select * from Asistencia
	where Fecha >= @fechaInicio
	and Fecha <= @fechaFin		
end	
go  



